﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading.Tasks;
using DataTier;
using System.Data;

namespace DataAccessLayer
{
    public class DBCustomer
    {
        private static SqlCommand dbCmd = null;

        public static IList<Customer> GetCustomers()
        {
            IList<Customer> returnList = new List<Customer>();
            string sql = "select * from Customer, ZipCode where Customer.cZip = ZipCode.zipCode";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Customer c;

            while (dbReader.Read())
            {
                c = new Customer((int)dbReader["cId"], dbReader["cUserName"].ToString(), dbReader["cName"].ToString(), dbReader["cAddress"].ToString(), (int)dbReader["cZip"], dbReader["city"].ToString(), dbReader["cEmail"].ToString(), dbReader["cPassword"].ToString());
                returnList.Add(c);
            }
            AccessDBSQLClient.Close();
            return returnList;

        }

        public static Customer GetCustomerByEmail(string email)
        {

            string sql = "select * from Customer, ZipCode where cZip = zipCode and cEmail = '" + email+"'";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Customer c;

            if (dbReader.Read())
                c = new Customer((int)dbReader["cId"], dbReader["cUserName"].ToString(), dbReader["cName"].ToString(), dbReader["cAddress"].ToString(), (int)dbReader["cZip"], dbReader["city"].ToString(), dbReader["cEmail"].ToString(), dbReader["cPassword"].ToString());
            else c = null;
            dbReader.Close();
            AccessDBSQLClient.Close();
            return c;

        }
        public static Customer GetCustomerByUserName(string username)
        {

            string sql = "select * from Customer, ZipCode where cZip = zipCode and cUserName = '" + username + "'";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Customer c;

            if (dbReader.Read())
                c = new Customer((int)dbReader["cId"], dbReader["cUserName"].ToString(), dbReader["cName"].ToString(), dbReader["cAddress"].ToString(), (int)dbReader["cZip"], dbReader["city"].ToString(), dbReader["cEmail"].ToString(), dbReader["cPassword"].ToString());
            else c = null;
            dbReader.Close();
            AccessDBSQLClient.Close();
            return c;

        }

        public static void CreateCustomer(string uName, string name, string address, int zip, string email, string password)
        {
            string sql = @"insert into customer values ('" + uName + "','" + name +"','" + address + "','" + zip + "','" + email + "','" + password + "')";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void DeleteCustomerByEmail(string email)
        {
            string sql = @"delete from customer where cEmail = ('" + email + "')";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static void DeleteCustomerById(int id)
        {
            string sql = @"delete from customer where cId = ('" + id + "')";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }


        public static Customer FindCustomerByNo(int custNo)
        {
            string sql = @"select * from Customer where cId = ' 1 '";
                                      dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Customer c;

            if (dbReader.Read())
                //c = new Customer(Convert.ToInt32(dbReader["custNo"].ToString()),
                //    dbReader["name"].ToString());
                c = new Customer((int)(dbReader["cId"]),
                    dbReader["cName"].ToString());
            else
            {
                c = null;
            }
            dbReader.Close();
            AccessDBSQLClient.Close();
            return c;
        }

        //public static Customer FindCustomerByName(string email)
        //{
        //    string sql = @"select * from Customer where name = '"
        //                    + custName+@"'";
        //    dbCmd = AccessDBSQLClient.GetDbCommand(sql);
        //    IDataReader dbReader;

        //    dbReader = dbCmd.ExecuteReader();

        //    Customer c;

        //    if (dbReader.Read())
        //        //c = new Customer(Convert.ToInt32(dbReader["custNo"].ToString()),
        //        //    dbReader["name"].ToString());
        //        c = new Customer((int)(dbReader["custNo"]),
        //            dbReader["name"].ToString());
        //    else
        //        c = null;
        //    dbReader.Close();
        //    AccessDBSQLClient.Close();
        //    return c;
        //}

        public static Customer GetCustomerByLoginEmail(String eMail)
        {
            String sql = @"select cPassword from Customer where cEmail = '" + eMail+@"'";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Customer c;

            if (dbReader.Read())
                c = new Customer(dbReader["cPassword"].ToString());
            else
                c = null;
            dbReader.Close();
            AccessDBSQLClient.Close();
            return c;

        }

        public static void UpdateCustomer(int id, string uName, String name, String address, int zip, String email, String password)
        {
            string sql = @"update Customer set cUserName = '" + uName + "', cName = '" + name + "', cAddress = '" + address + "', cZip = '" + zip + "', cEmail = '" + email + "', cPassword = '" + password + "' where cId = '" + id + "'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
        }


        public static void UpdateSubscription(int subId, int subTypeId, int carId, int batteryId, int customerId, int carModelId)
        {
            string sql = @"update Subscription set stId = '"+subTypeId+"', carId = '" +carId+"', batteryId = '"+batteryId+"', cId = '"+customerId+"', cmId ='"+carModelId+"' where sId ='"+subId+"'";
            SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
            cmd.ExecuteNonQuery();
            AccessDBSQLClient.Close();
        }

        public static String GetCityFromZip(int zip)
        {
            string sql = @"select city from ZipCode where zipcode = '" + zip + "'";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();
            string result = null;
            if (dbReader.Read())

                result = dbReader["city"].ToString();
            else
                result = null;
            dbReader.Close();
            AccessDBSQLClient.Close();
            return result;
        }
    }
//}
}
